package edu.uoc.tds.pec3.servidor;

import edu.uoc.tds.pec3.comun.PEC3Exception;
import edu.uoc.tds.pec3.comun.RemotoInterface;
import edu.uoc.tds.pec3.comun.ConceptoGasto;
import edu.uoc.tds.pec3.comun.Evento;
import edu.uoc.tds.pec3.comun.EventoGasto;
import edu.uoc.tds.pec3.comun.Factura;
import edu.uoc.tds.pec3.comun.Proveedor;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 *
 * @author JUAN ANTONIO
 */
public class RemotoImpl extends UnicastRemoteObject
        implements RemotoInterface, Serializable {
    private static final long serialVersionUID = 1L;
    private Connection conexion;
    private String contrasenyaBBDD;
    private String urlBBDD;
    private String usuarioBBDD;
    
    public RemotoImpl() throws RemoteException, PEC3Exception {
        super();
        conectarBBDD();
    }

    private void conectarBBDD() throws PEC3Exception {
        Properties properties = new Properties();

        try {
            Class.forName("org.postgresql.Driver");
            File file = new File("properties/configuration.properties");
            if (!file.exists()){
                file = new File("src/properties/configuration.properties");
            }
            FileInputStream fileinputstream = new FileInputStream(file);
            properties.load(fileinputstream);
            urlBBDD = properties.getProperty("url");
            usuarioBBDD = properties.getProperty("username");
            contrasenyaBBDD = properties.getProperty("password");
            conexion = DriverManager.getConnection(urlBBDD, usuarioBBDD, contrasenyaBBDD);

        } catch (ClassNotFoundException e) {
            throw new PEC3Exception(PEC3Exception.ERR_CONE_SERV);

        } catch (FileNotFoundException e) {
           throw new PEC3Exception(PEC3Exception.ERR_NOT_FILE);

        } catch (IOException e) {
           throw new PEC3Exception(PEC3Exception.ERR_PROPERTIES);

        } catch (SQLException e) {
            throw new PEC3Exception(PEC3Exception.ERR_BD);
        }
    }

    private void desconectarBBDD() throws RemoteException, PEC3Exception {
        try {
            conexion.close();
            System.out.println("Desconectado de la BD!!");

        } catch (SQLException sqlexception) {
            throw new PEC3Exception(PEC3Exception.ERR_BD);
        }
    }

    public ArrayList<ConceptoGasto> getConceptos() throws RemoteException {
        ArrayList<ConceptoGasto> lista = new ArrayList<ConceptoGasto>();
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM concepto_gasto";

            rs = st.executeQuery(query);
            while (rs.next()) {
                ConceptoGasto concepto = new ConceptoGasto
                        (rs.getLong("id_concepto"), rs.getString("descripcion"));
                lista.add(concepto);
            }

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return lista;
    }

    public ArrayList<Evento> getEventos() throws RemoteException {
        ArrayList<Evento> lista = new ArrayList<Evento>();
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM evento";

            rs = st.executeQuery(query);
            while (rs.next()) {
                Evento evento = new Evento
                        (rs.getLong("id_evento"), rs.getString("descripcion"),
                        rs.getDate("fecha_inicio"), rs.getDate("fecha_fin"),
                        rs.getDate("fecha_alta"));
                lista.add(evento);
            }

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return lista;
    }

    public ArrayList<Proveedor> getProveedores() throws RemoteException {
        ArrayList<Proveedor> lista = new ArrayList<Proveedor>();
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM proveedor";

            rs = st.executeQuery(query);
            while (rs.next()) {
                Proveedor proveedor = new Proveedor
                        (rs.getLong("id_proveedor"), rs.getString("nombre_entidad"),
                        rs.getString("cif"), rs.getDate("fecha_alta"));
                lista.add(proveedor);
            }

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return lista;
    }

    public void altaFactura(Factura factura) throws RemoteException {
        PreparedStatement st = null;

 	try {
            String query = "INSERT INTO factura (id_evento, id_concepto, id_proveedor,"
                    + " descripcion, fecha_valor, importe, fecha_alta) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?)";

            st = conexion.prepareStatement
                    (query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            st.setLong(1,factura.getEvento().getIdEvento());
            st.setLong(2, factura.getConcepto().getIdConcepto());
            st.setLong(3,factura.getProveedor().getIdProveedor());
            st.setString(4,factura.getDescripcion());
            st.setDate(5, (Date) factura.getFechaValor());
            st.setDate(7, (Date) factura.getFechaAlta());
            st.execute();

 	} catch (SQLException e) {
            e.printStackTrace();
            
 	} finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    
    // JUAN //
    public ArrayList<Factura> getFacturas(ConceptoGasto concepto) throws RemoteException {
        ArrayList<Factura> lista = new ArrayList<Factura>();
        PreparedStatement st = null;
 	ResultSet rs = null;

        try {
            String query = "SELECT * FROM factura WHERE id_concepto = ?";

            st = conexion.prepareStatement
                    (query,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            st.setLong(1,concepto.getIdConcepto());

            rs = st.executeQuery(query);
            while (rs.next()) {
                Evento evento = getEvento(rs.getLong("id_evento"));
                Proveedor proveedor = getProveedor(rs.getLong("id_evento"));
                Factura factura = new Factura(evento, concepto, proveedor,
                        rs.getString("descripcion"),
                        (java.util.Date) rs.getDate("fecha_valor"),
                        rs.getLong("importe"),
                        (java.util.Date) rs.getDate("fecha_alta"));
                lista.add(factura);
            }

 	} catch (SQLException e) {
            e.printStackTrace();

 	} finally {
            try {
                if (st != null) {
                    st.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
 	return lista;
    }

    public ConceptoGasto getConcepto(long idConcepto) throws RemoteException{
        ConceptoGasto concepto = null;
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM concepto_gasto WHERE id_concepto = "
                    + idConcepto;

            rs = st.executeQuery(query);
            concepto = new ConceptoGasto(rs.getLong("id_concepto"),
                    rs.getString("descripcion"));

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return concepto;
    }

    public Evento getEvento(long idEvento) throws RemoteException{
        Evento evento = null;
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM evento WHERE id_evento = "
                    + idEvento;

            rs = st.executeQuery(query);
            evento = new Evento(rs.getLong("id_evento"), rs.getString("descripcion"),
                    (java.util.Date) rs.getDate("fecha_inicio"),
                    (java.util.Date) rs.getDate("fecha_fin"),
                    (java.util.Date) rs.getDate("fecha_alta"));

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return evento;
    }

    public Proveedor getProveedor(long idProveedor) throws RemoteException{
        Proveedor proveedor = null;
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
            String query = "SELECT * FROM proveedor WHERE id_proveedor = "
                    + idProveedor;

            rs = st.executeQuery(query);
            proveedor = new Proveedor(rs.getLong("id_proveedor"),
                    rs.getString("nombre_entidad"), rs.getString("cif"),
                    (java.util.Date) rs.getDate("fecha_alta"));

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return proveedor;
    }
    // JUAN //

    //YOLI//
    public ArrayList<EventoGasto> getEventog(Evento evento, Proveedor proveedor)
             throws RemoteException {
        ArrayList<EventoGasto> lista = new ArrayList<EventoGasto>();
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);


            String query = "Select to_char(factura.fecha_valor,'DD/MM/YYYY'), proveedor.nombre_entidad, " +
                      "concepto_gasto.descripcion, factura.descripcion , factura.importe " +
                      " from factura, proveedor, concepto_gasto, evento "  +
                      " where evento.id_evento = " + evento.getIdEvento()  ;
            if (proveedor.getIdProveedor() != 0){
                query += " and proveedor.id_proveedor = " + proveedor.getIdProveedor();
            }

                query += " and evento.id_evento = factura.id_evento " +
                      " and proveedor.id_proveedor = factura.id_proveedor" ;


            rs = st.executeQuery(query);
            while (rs.next()) {
             EventoGasto eventog = new EventoGasto (rs.getString("fecha_valor"),
                      rs.getString("nombre_entidad"),
                      rs.getString("descripcion"),
                      rs.getString("descripcion"),
                       rs.getLong("importe"));


                lista.add(eventog);
            }

 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return lista;
    }

     public  EventoGasto getEventoImporte (long id_evento, long id_proveedor)
             throws RemoteException {
        EventoGasto eventoimporte = null;
        Statement st = null;
 	ResultSet rs = null;

 	try {
            st = conexion.createStatement
                    (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);


            String query = "Select sum(factura.importe) " +
                      " from factura, proveedor, concepto_gasto, evento "  +
                      " where evento.id_evento = " + id_evento  ;
            if (id_proveedor != 0){
                query += " and proveedor.id_proveedor = " + id_proveedor;
            }

                query += " and evento.id_evento = factura.id_evento " +
                      " and proveedor.id_proveedor = factura.id_proveedor" ;


            rs = st.executeQuery(query);


 	} catch (SQLException e) {
 		e.printStackTrace();

 	} finally {
            try {
                rs.close();
                st.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
 	}
 	return eventoimporte;
    }
    //YOLI//
 }